package com.xhu.dao.impl;

import com.xhu.bean.pojo.SalesPlan;
import com.xhu.dao.SpDao;
import com.xhu.utils.ConnectionFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SpDaoImpl implements SpDao {

    @Override
    public List<SalesPlan> findAll() throws SQLException {
        String sql="select saleplan_id,emp_name,status_name," +
                "planner_name,planner_month,saleplan_code,current_name,dispose_name" +
                " from saleplan";
        Connection connection= ConnectionFactory.openConnection();
        QueryRunner queryRunner=new QueryRunner();


        List<SalesPlan>  list = queryRunner.query(connection, sql, new BeanListHandler<>(SalesPlan.class));
        ConnectionFactory.closeConnection(connection);

        return list;
    }

    @Override
    public int add(SalesPlan salesPlan) {
        String sql="INSERT INTO " +
                " saleplan(emp_name,status_name,planner_name,planner_month," +
                "saleplan_code,current_name,planner_text,dispose_name,last_time) " +
                " VALUE(?,?,?,?,?,?,?,?,?) ";
        Connection connection=ConnectionFactory.openConnection();
        QueryRunner queryRunner=new QueryRunner();

        try {
            return queryRunner.update(connection,sql,
                    salesPlan.getEmp_name(),
                    "未提交",
                    salesPlan.getPlanner_name(),
                    salesPlan.getPlanner_month(),
                    salesPlan.getSaleplan_code(),
                    salesPlan.getCurrent_name(),
                    salesPlan.getPlanner_text(),
                    salesPlan.getCurrent_name(),
                    salesPlan.getLast_time());
        } catch (SQLException e) {

            throw new RuntimeException(e);
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
    }

    @Override
    public List<SalesPlan> queryPage(Integer start, Integer limit) {

        List<Object> parms=new ArrayList<>();

        StringBuffer sql=new StringBuffer(" select * from saleplan ");
        sql.append(" limit ?,? ");
        parms.add(start);
        parms.add(limit);

        Connection connection=ConnectionFactory.openConnection();
        QueryRunner queryRunner=new QueryRunner();

        List<SalesPlan> list=null;
        try {
            list=queryRunner.query(connection,sql.toString(),parms.toArray(),new BeanListHandler<>(SalesPlan.class));


        } catch (SQLException e) {
            System.out.println("list中的sql出现问题");
            throw new RuntimeException(e);
        }finally {
            ConnectionFactory.closeConnection(connection);
        }


        return list;
    }

    @Override
    public int subm(String saleplan_id) {
        String sql=" UPDATE saleplan " +
                " set status_name=? " +
                " where saleplan_id=? ";
        Connection connection=ConnectionFactory.openConnection();
        QueryRunner queryRunner=new QueryRunner();

        try {
            return queryRunner.update(connection,sql,"已提交",saleplan_id);
        } catch (SQLException e) {

            throw new RuntimeException(e);
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
    }

    @Override
    public int upData(SalesPlan salesPlan, String saleplan_id) {
        String sql=" UPDATE saleplan " +
                " set saleplan_code=?,planner_name=?,planner_month=?,planner_text=? " +
                " where saleplan_id=? ";
        Connection connection=ConnectionFactory.openConnection();
        QueryRunner queryRunner=new QueryRunner();

        try {
            return queryRunner.update(connection,sql,salesPlan.getSaleplan_code(),
                                                    salesPlan.getPlanner_name(),
                                                    salesPlan.getPlanner_month(),
                                                    salesPlan.getPlanner_text(),saleplan_id);
        } catch (SQLException e) {

            throw new RuntimeException(e);
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
    }

    @Override
    public int del(String saleplan_id) {
        String sql=" DELETE FROM saleplan " +
                " WHERE saleplan_id=? ";
        Connection connection=ConnectionFactory.openConnection();
        QueryRunner queryRunner=new QueryRunner();

        try {
            return queryRunner.update(connection,sql,saleplan_id);
        } catch (SQLException e) {

            throw new RuntimeException(e);
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
    }

    @Override
    public List<SalesPlan> queryPage(Integer start, Integer limit, String search_name) {
        List<Object> parms=new ArrayList<>();

        StringBuffer sql=new StringBuffer(" select * from saleplan where saleplan_code like '%" +search_name+ "%'");
        sql.append(" limit ?,? ");
        parms.add(start);
        parms.add(limit);

        Connection connection=ConnectionFactory.openConnection();
        QueryRunner queryRunner=new QueryRunner();

        List<SalesPlan> list=null;
        try {
            list=queryRunner.query(connection,sql.toString(),parms.toArray(),new BeanListHandler<>(SalesPlan.class));


        } catch (SQLException e) {
            System.out.println("list中的sql出现问题");
            throw new RuntimeException(e);
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
        return list;
    }
}
